Logical and Physical Modeling for Analytical Applications

A bad data model leads to an application that does not perform well. Therefore, when developing an application, you should create a good data model from the start. However, even the best logical model can’t help when the physical implementation is bad. It is also important to know how SQL Server stores and accesses data, and how to optimize the data access. Database optimization starts by splitting transactional and analytical applications. In this course, you learn how to support analytical applications with logical design, get understanding of the problems with data access for queries that deal with large amounts of data, and learn about SQL Server optimizations that help solving these problems.

Logical and Physical Modeling for Analytical Applications Star and Snowflake Schemask 00:20:19
Introduction 02:43
Normalized Schema 03:51
Star Schema 02:29
Shared Dimensions 00:36
Dimension Denormalization 00:42
Snowflake Schema 00:52
Hybrid Approach 01:14
Granularity 02:03
Auditing Lineage 01:39
DWTerms 02:59
Summary 01:11
Logical and Physical Modeling for Analytical Applications Designing Dimensions and Fact Tables 00:38:19
Introduction 00:53
Dimension Columns 02:27
Hierarchies 02:14
SCD Classic 03:15
Referencing Monster 02:38
SCD Custom 02:24
Demo Dimensions 05:45
Fact Columns 01:30
Measures Additivity 02:51
Demo Fact 02:53
MtoM 03:21
Demo MtoM 07:10
Summary 00:58
Logical and Physical Modeling for Analytical Applications Data Warehousing Data Access Problems 00:21:49
Introduction 01:40
Algorithms Complexity 01:51
SSAS Addressing 02:34
Joins 02:32
Linearize Joins 01:38
Bitmap 01:04
Bloom 03:28
Trans-Relational 01:30
Columnar Storage 03:54
Summary 01:38
Logical and Physical Modeling for Analytical Applications SQL Server Data Warehousing Support before Version 2012 00:33:18
Introduction 01:05
Partitioned Table 03:10
Sliding Window 04:26
Demo Partitioning 05:45
Filtered Index 01:39
Indexed View 02:14
Demo Filtered Indexed 06:34
Data Compression 05:20
Demo Data Compression 01:57
Summary 01:08
Logical and Physical Modeling for Analytical Applications Sql Server Data Warehousing Optimizations in Version 2012 00:34:01
Introduction 00:56
Window Functions 02:48
Demo Window Fun 04:45
Columnstore Indexes 01:19
Columnstore Compression 02:48
Reducing IO 01:36
Reducing CPU Batch 03:22
Columnstore Restrictions 02:56
Data Loading Usage 02:40
Demo Columnstore Batch 07:26
Version 2014 Summary 03:25

Star and Snowflake Schemask	00:20:19
Introduction	02:43
Normalized Schema	03:51
Star Schema	02:29
Shared Dimensions	00:36
Dimension Denormalization	00:42
Snowflake Schema	00:52
Hybrid Approach	01:14
Granularity	02:03
Auditing Lineage	01:39
DWTerms	02:59
Summary	01:11
Designing Dimensions and Fact Tables	00:38:19
Introduction	00:53
Dimension Columns	02:27
Hierarchies	02:14
SCD Classic	03:15
Referencing Monster	02:38
SCD Custom	02:24
Demo Dimensions	05:45
Fact Columns	01:30
Measures Additivity	02:51
Demo Fact	02:53
MtoM	03:21
Demo MtoM	07:10
Summary	00:58
Data Warehousing Data Access Problems	00:21:49
Introduction	01:40
Algorithms Complexity	01:51
SSAS Addressing	02:34
Joins	02:32
Linearize Joins	01:38
Bitmap	01:04
Bloom	03:28
Trans-Relational	01:30
Columnar Storage	03:54
Summary	01:38
SQL Server Data Warehousing Support before Version 2012	00:33:18
Introduction	01:05
Partitioned Table	03:10
Sliding Window	04:26
Demo Partitioning	05:45
Filtered Index	01:39
Indexed View	02:14
Demo Filtered Indexed	06:34
Data Compression	05:20
Demo Data Compression	01:57
Summary	01:08
Sql Server Data Warehousing Optimizations in Version 2012	00:34:01
Introduction	00:56
Window Functions	02:48
Demo Window Fun	04:45
Columnstore Indexes	01:19
Columnstore Compression	02:48
Reducing IO	01:36
Reducing CPU Batch	03:22
Columnstore Restrictions	02:56
Data Loading Usage	02:40
Demo Columnstore Batch	07:26
Version 2014 Summary	03:25